using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using XProject.Entity;
namespace XProject.DAO
{
   class DiaryDAO:DBAccess
   {
       #region sql command
       string SQL_SELECT_ID = "select max(ID) from Diary";
       string SQL_SELECT_BY_ID = "select * from Diary where ID=@diaryId";
       string SQL_SELECT_BY_DATE = "select * from Diary where datepart(\"yyyy\",Date)=datepart(\"yyyy\",@date) and datepart(\"m\",Date)=datepart(\"m\",@date) and datepart(\"d\",Date)=datepart(\"d\",@date) and AccountID=@accountID";
       string SQL_INSERT = "insert into Diary(AccountID, [Date], Title, Status, Content, FeelingIconID, Encrypted) values(@accountID, @date, @title, @status, @content, @iconId, @encrypted)";
       string SQL_UPDATE = "update Diary set Title=@title, Status=@status, Content=@content, FeelingIconId=@iconId, Encrypted=@encrypted where ID=@diaryID";
       string SQL_DELETE = "Delete from Diary where ID=@diaryID";
       string SQL_SELECT_FIRST_DATE = "select min([Date]) from Diary";
       string SQL_SELECT_LAST_DATE = "select max([Date]) from Diary";
       #endregion
       #region field
       string message;
       IconDAO daoIcon=null;
       #endregion
       #region properties
       public string Message
       {
           get { return message; }
           set { message = value; }
       }
       #endregion
       #region constructor
       public DiaryDAO() {
           daoIcon = new IconDAO();
       }
       #endregion
       #region method
       public DiaryItem GetDiaryByID(int diaryId)
       {
           DiaryItem diary = new DiaryItem();
           try
           {
               OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_ID, conn);
               command.Parameters.Add("@diaryId", OleDbType.Integer).Value = diaryId;
               if (Connect())
               {
                   OleDbDataReader dr = command.ExecuteReader();
                   if (dr.Read())
                   {
                       diary.ItemID = dr.GetInt32(0);
                       diary.Account.AccountID = dr.GetInt32(1);
                       diary.Date = dr.GetDateTime(2);
                       diary.Title = dr.GetString(3);
                       diary.Status = dr.GetString(4);
                       diary.Content = dr.GetString(5);
                       diary.Icon.IconID = dr.GetInt32(6);
                       diary.Encrypted = dr.GetBoolean(7);
                   }
                   Disconnect();
               }
           }
           catch (OleDbException ex)
           {
               message = ex.Message;
           }
           catch (Exception ex)
           {
               message = ex.Message;
           }
           return diary;

       }
       public DiaryItem GetDiaryByDate(DateTime date, int accountID)
       {
           DiaryItem diary = new DiaryItem();
           message = "";
           try
           {
               OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_DATE, conn);
               command.Parameters.Add("@date", OleDbType.DBDate).Value=date;
               command.Parameters.Add("@accountID",OleDbType.Integer).Value=accountID;
               if (Connect())
               {
                   OleDbDataReader dr = command.ExecuteReader();
                   if (dr.Read())
                   {
                       diary.ItemID = dr.GetInt32(0);
                       diary.Account.AccountID = dr.GetInt32(1);
                       diary.Date = dr.GetDateTime(2);
                                         
                           diary.Title = dr.GetString(3);
                           diary.Status = dr.GetString(4);
                           diary.Content = dr.GetString(5);                 
                       
                           diary.Icon.IconID = dr.GetInt32(6);
                       diary.Encrypted = dr.GetBoolean(7);
                       diary.Icon = daoIcon.GetIcon(diary.Icon.IconID);
                   }
                   Disconnect();
               }
           }
           catch (OleDbException ex)
           {
               message = ex.Message;
           }
           catch (Exception ex)
           {
               message = ex.Message;
           }
           return diary;
           
       }
       public int Insert(DiaryItem diary)
       {
           int effectedRows = 0;
           try
           {
               OleDbCommand command = new OleDbCommand(SQL_INSERT, conn);
               command.Parameters.Add("@accountID",OleDbType.Integer).Value=diary.Account.AccountID;
               command.Parameters.Add("@date", OleDbType.Date).Value = diary.Date;               
               if (!diary.Encrypted)
               {
                    command.Parameters.Add("@title", OleDbType.VarChar).Value = diary.Title;
                   command.Parameters.Add("@status", OleDbType.VarChar).Value = diary.Status;
                   command.Parameters.Add("@content", OleDbType.VarChar).Value = diary.Content;
                   
               }
               else
               {
                   command.Parameters.Add("@title", OleDbType.VarChar).Value = diary.EncryptedTitle;
                   command.Parameters.Add("@status", OleDbType.VarChar).Value = diary.EncryptedStatus;
                   command.Parameters.Add("@content", OleDbType.VarChar).Value = diary.EncryptedContent;
               }
               command.Parameters.Add("@iconId",OleDbType.Integer).Value=diary.Icon.IconID;
               command.Parameters.Add("@encrypted", OleDbType.Boolean).Value = diary.Encrypted;
               if (Connect())
               {
                   effectedRows = command.ExecuteNonQuery();
                   command.CommandText = SQL_SELECT_ID;
                   diary.ItemID = int.Parse(command.ExecuteScalar().ToString());
                   Disconnect();
               }
           }
           catch (OleDbException ex)
           {
               message = ex.Message;
           }
           catch (Exception ex)
           {
               message = ex.Message;
           }
           return effectedRows;
       }

       public int Update(DiaryItem diary)
       {
           int effectedRows = 0;
           try
           {
               OleDbCommand command = new OleDbCommand(SQL_UPDATE, conn);
               if (!diary.Encrypted)
               {
                   command.Parameters.Add("@title", OleDbType.VarChar).Value = diary.Title;
                   command.Parameters.Add("@status", OleDbType.VarChar).Value = diary.Status;
                   command.Parameters.Add("@content", OleDbType.VarChar).Value = diary.Content;

               }
               else
               {
                   command.Parameters.Add("@title", OleDbType.VarChar).Value = diary.EncryptedTitle;
                   command.Parameters.Add("@status", OleDbType.VarChar).Value = diary.EncryptedStatus;
                   command.Parameters.Add("@content", OleDbType.VarChar).Value = diary.EncryptedContent;
               }
               command.Parameters.Add("@iconId", OleDbType.Integer).Value = diary.Icon.IconID;
               command.Parameters.Add("@encrypted", OleDbType.Boolean).Value = diary.Encrypted;
               command.Parameters.Add("@diaryID", OleDbType.Integer).Value = diary.ItemID;
               if (Connect())
               {
                   effectedRows = command.ExecuteNonQuery();                   
                   Disconnect();
               }
           }
           catch (OleDbException ex)
           {
               message = ex.Message;
           }
           catch (Exception ex)
           {
               message = ex.Message;
           }
           return effectedRows;
       }
       public int Delete(DiaryItem diary)
       {
           int effectedRows = 0;
           try
           {
               OleDbCommand command = new OleDbCommand(SQL_DELETE, conn);
               command.Parameters.Add("@diaryID", OleDbType.Integer).Value = diary.ItemID;
               if (Connect())
               {
                   effectedRows = command.ExecuteNonQuery();                  
                   Disconnect();
               }
           }
           catch (OleDbException ex)
           {
               message = ex.Message;
           }
           catch (Exception ex)
           {
               message = ex.Message;
           }
           return effectedRows;
       }
       public DateTime GetFirstDate()
       {
           OleDbCommand command = new OleDbCommand(SQL_SELECT_FIRST_DATE,conn);
           DateTime date = new DateTime();
           if (Connect())
           {
               OleDbDataReader dr = command.ExecuteReader();
               if (dr.Read()) if(!dr.IsDBNull(0)) date = dr.GetDateTime(0);
           }
           return date;
       }
       public DateTime GetLastDate()
       {
           OleDbCommand command = new OleDbCommand(SQL_SELECT_LAST_DATE, conn);
           DateTime date = new DateTime();
           if (Connect())
           {
               OleDbDataReader dr = command.ExecuteReader();
               if (dr.Read()) date = dr.GetDateTime(0);
           }
           return date;
       }
       #endregion
   }
}
